import hashlib
import random
import time

import pymysql


# 数据库连接是在\
# create_db_connection
# 函数中创建的，并且设置了
# autocommit = True。这意味着每次执行
# DML（数据操纵语言，如
# INSERT, UPDATE, DELETE）操作时，更改会立即被提交到数据库而无需显式调用
# commit()
# 方法。

def create_db_connection():
    """创建数据库连接"""
    return pymysql.connect(
        host='localhost',
        user='root',
        password='123456',
        port=3306,
        database='test',
        autocommit=True,
        connect_timeout=6000
    )


def with_db_connection(func):
    """装饰器，用于管理数据库连接"""

    def wrapper(*args, **kwargs):
        with create_db_connection() as db:
            cursor = db.cursor()
            result = func(cursor, *args, **kwargs)
            return result

    return wrapper


@with_db_connection
def sql_add_Account(cursor, account, password):
    """
    添加账户
    :param cursor: 数据库游标
    :param account: 账户名
    :param password: 密码
    :return: None
    """
    _password = hashlib.md5(password.encode('utf-8')).hexdigest()
    random_part = random.randint(1, 1000)
    _id = ((((time.time() * random_part * 10) / random.randint(1, 100))
           * random.randint(1, 10))/
           random.randint(1, 1000))
    sql = "INSERT INTO pytest (id,account, password) VALUES (%s,%s, %s)"
    cursor.execute(sql, (_id, account, _password))
    print('添加成功')

@with_db_connection
def sql_select_money_byId(cursor, uid):
    """
    查询账户余额
    :param cursor: 数据库游标
    :param uid: 用户
    """
    sql = ("SELECT account_money FROM \
    pytest WHERE id = %s")
    cursor.execute(sql, (uid,))
    data = cursor.fetchall()
    return data


# data = sql_select_money_byId(1)
# print(data[0][0])


@with_db_connection
def sql_select_Account(cursor):
    """
    查询账户
    :param cursor: 数据库游标
    :return: 所有账户数据
    """
    sql = "SELECT * FROM pytest"
    cursor.execute(sql)
    data = cursor.fetchall()
    return list(data)


@with_db_connection
def sql_select_Account_by_id(cursor, uid):
    """
    查询账户通过account用户名
    :param cursor: 数据库游标
    :param uid: 用户ID
    :return: 所有账户数据
    """
    sql = "SELECT * FROM pytest WHERE id = %s"
    cursor.execute(sql, (uid,))
    data = cursor.fetchall()
    return list(data)


@with_db_connection
def sql_select_Account_by_account(cursor, _account):
    """
    查询账户通过account用户名
    :param cursor: 数据库游标
    :param _account: 账户名
    :return: 匹配的账户数据
    """
    sql = "SELECT * FROM pytest WHERE account = %s"
    cursor.execute(sql, (_account,))
    data = cursor.fetchall()
    return list(data)


@with_db_connection
def sql_Login_by_account(cursor, account, password):
    """
    查询账户通过account用户名
    :param cursor: 数据库游标
    :param account: 账户名
    :param password: 密码
    :return: 匹配的账户数据
    """
    _password = hashlib.md5(password.encode('utf-8')).hexdigest()
    # print(_password)
    sql = "SELECT * FROM pytest WHERE account = %s AND password = %s"
    cursor.execute(sql, (account, _password))
    data = cursor.fetchall()
    return list(data)


# 在Python中，%d 是用来格式化整数的占位符，而 %s 通常用于字符串和其他类型的数据。
# 在SQL语句中，为了保持一致性和安全性，通常使用 %s 作为通用占位符。具体原因如下：
# 通用性：%s 可以处理多种数据类型，包括字符串、整数和浮点数。
# 安全性：使用 %s 可以避免 SQL 注入攻击，因为实际值会通过参数化查询传递。
# 一致性：在数据库操作中，统一使用 %s 可以简化代码编写和维护。
# 因此，在这个上下文中，使用 %s 更为合适。虽然 %d 可以用于格式化整数，但在 SQL 查询中并不推荐这样做。
@with_db_connection
def sql_set_transaction_history(cursor, account, money, transaction_type, accepter_name):
    """
    记录
    :param cursor:
    :param account:
    :param money:
    :param transaction_type:
    :param accepter_name:
    :return:
    """
    if transaction_type == '1' or transaction_type == '2':
        sql = "INSERT INTO transactions (account_name, amount, transaction_type) VALUES (%s, %s, %s)"
        cursor.execute(sql, (account, money, transaction_type,))
        print('记录添加成功')
    elif transaction_type == '3':
        sql = "INSERT INTO transactions (account_name, amount, transaction_type, accepter_name) VALUES (%s, %s, %s, %s)"
        cursor.execute(sql, (account, money, transaction_type, accepter_name,))
        print('记录添加成功')


@with_db_connection
def sql_update_money(cursor, account, money, ):
    """
    更新账户余额
    :param cursor: 数据库游标
    :param account: 账号
    :param money: 修改的金额
    :return: 更新后的余额
    """
    sql = "UPDATE pytest SET account_money = account_money + %s WHERE account = %s"
    cursor.execute(sql, (money, account))

    if money < 0:
        # //取款
        type = '2'
    elif money > 0:
        # //存款
        type = '1'

    print('更新成功')
    if type == '1' or type == '2':
        sql_set_transaction_history(account=account, money=money, transaction_type=type, accepter_name=None)
    sql_before = "SELECT account_money FROM pytest WHERE account = %s"
    cursor.execute(sql_before, (account,))
    data = cursor.fetchall()
    return list(data)


@with_db_connection
def sql_update_money_Fu(cursor, account, money, ):
    """
    更新账户余额
    :param cursor: 数据库游标
    :param account: 账号
    :param money: 修改的金额
    :return: 更新后的余额
    """
    sql = "UPDATE pytest SET account_money = account_money + %s WHERE account = %s"
    cursor.execute(sql, (money, account))
    print('更新成功')
    sql_before = "SELECT account_money FROM pytest WHERE account = %s"
    cursor.execute(sql_before, (account,))
    data = cursor.fetchall()
    return list(data)


# data = sql_update_money(account='jack', money=-100)
# print(data)
@with_db_connection
def sql_account_trans(cursor, money, sender, receiver):
    """
    转账
    :param cursor: 数据库游标
    :param money: 金额
    :param sender: 转账人
    :param receiver: 收款人
    :return: None
    """
    type = '3'
    sql_update_money_Fu(receiver, int(money), )
    data = sql_update_money_Fu(sender, -int(money), )
    sql_set_transaction_history(account=sender, money=int(money), transaction_type=type, accepter_name=receiver)
    sql_before = "SELECT account_money FROM pytest WHERE account = %s"
    cursor.execute(sql_before, (sender,))
    return data


# 调用函数
# data = sql_account_trans(money=100, sender='jack', receiver='ck')
# print(data)
# # sql_set_transaction_history(account='jack', money='100', transaction_type='3', accepter_name='ck')


@with_db_connection
def sql_update_password(cursor, account, password):
    """
    更新密码
    :param cursor: 数据库游标
    :param account: 账号
    :param password: 新密码
    :return: None
    """
    password = hashlib.md5(password.encode('utf-8')).hexdigest()
    sql = "UPDATE pytest SET password = %s WHERE account = %s"
    cursor.execute(sql, (password, account))


@with_db_connection
def sql_delete_account(cursor, id):
    """
    删除账户
    :param cursor: 数据库游标
    :param id: 用户ID
    :return: None
    """
    sql = "DELETE FROM pytest WHERE id = %s"
    cursor.execute(sql, (id,))
    print('删除成功')


@with_db_connection
def sql_verify_password(cursor, id, password):
    """
    验证密码
    :param cursor: 数据库游标
    :param id: 用户ID
    :return: None
    """
    sql = 'select password from pytest where id=%s'
    cursor.execute(sql, (id,))
    try:
        data = cursor.fetchall()
        if data[0][0] == hashlib.md5(password.encode('utf-8')).hexdigest():
            return True
        else:
            return False
    except:
        return False


@with_db_connection
def sql_select_history_by_account(cursor, account, pagesize, page_number):
    """
    查询历史记录
    :param cursor: 数据库游标
    :param account: 账号
    :param pagesize: 每页大小
    :param page_number: 页数
    :return: None
    """
    sql = "SELECT * FROM transactions WHERE account_name = %s OR accepter_name = %s ORDER BY timestamp DESC LIMIT %s OFFSET %s "
    cursor.execute(sql, (account, account, pagesize, (page_number - 1) * pagesize,))
    data = cursor.fetchall()
    return data

# data=sql_select_history_by_account(account='jack', pagesize=3, page_number=2)
# print(data[0][5])
